const XLSX = require('xlsx')
const fs = require('fs')
const workbook = XLSX.readFile('./input/2023数据对应关系.xlsx')
const subWorkbook = XLSX.readFile('./input/数模231225.xlsx')

const getJSONoneSheet = (workbook, sheetName) => {
  //遍历每个工作表并转换为JSON
  const worksheet = workbook.Sheets[sheetName]
  return XLSX.utils.sheet_to_json(worksheet)
}

function subDescStr(str) {
  let startIndex = str.indexOf('1）实体定义：') + '1）实体定义：'.length
  let endIndex = str.indexOf('2）业务用途：')
  return str.substring(startIndex, endIndex)
}


function getArrayJOSN () { 
  let array = getJSONoneSheet(workbook, '数据对应关系')
  let subArray = getJSONoneSheet(subWorkbook, '物理模型')
  console.log('主表:', array.length)
  console.log("子表:",subArray.length)
  let jsonArray = array.map(item => {
    item.tableName = item.tableName.toUpperCase()

    item.feildArr = subArray.filter(subItem => {
      // if (subItem.cnName === item.cnName) {
      //   console.log("条件成立",item.cnName);
      // }
      let str = subItem['库表英文']
      if (str) {
        return str.toUpperCase() === item.tableName
      }
      return ''
    }).map(i => {
      return {
        fieldType: i['数据类型'],
        length: i['长度/精度'],
        float: i['小数位'],
        PK: i['主/外键'],
        fieldCnName: i['字段中文'],
        fieldName: i['字段英文'],
        tableName: i['库表英文'],
        cnName: i['库表中文'],
        desc: subDescStr(i['库表定义'])
      }
    })
    item.desc = item.feildArr[0]?item.feildArr[0].desc:''
    return item
  })
  
  return jsonArray
  // console.log(jsonArray[0])
}

// getArrayJOSN()



function createJSONFile (file, json) {
  const data = JSON.stringify(json, null, 2)
  fs.writeFileSync(file, data)
}

createJSONFile('./output/array.JSON', getArrayJOSN())

module.exports = getArrayJOSN
